package icasue.tools.sqlhelper.core;

import icasue.tools.sqlhelper.core.AbstractSqlProviderFrame;
import icasue.tools.sqlhelper.core.FrameParser;
import icasue.tools.sqlhelper.core.DBCol;
import icasue.tools.sqlhelper.core.SaveMode;
import lombok.SneakyThrows;
import icasue.reflect.annotations.NotNull;
import icasue.reflect.handles.fields.FieldOF;
import icasue.reflect.handles.object.ObjectOF;

import java.sql.SQLException;
import java.util.*;
import java.util.function.Consumer;
import java.util.stream.Collectors;

import static icasue.tools.sqlhelper.common.Const.*;

class Save extends FrameParser{


    @SneakyThrows(Throwable.class)
    final static String _chooseSaveMulti(@NotNull List<? extends Object> instMulti,
                                         @NotNull SaveMode saveMode,
                                         boolean chooseAble, AbstractSqlProviderFrame frame){
        // 保存列名序列.
        final StringBuilder columnsNameSeq = new StringBuilder();
        // 保存值序列.
        final List<StringBuilder> allValueSeq = new LinkedList<>();
        // Java属性与列有效映射.
        final Map<String, String> fName_column = $fName_To_Column.apply(frame);


        // 需要选择保存的列映射.
        final Map<String, DBCol> saveChooseMapping = new LinkedHashMap<>(frame.dbTab.mappingRoot);
        if(chooseAble){
            // 参考第一个存在的Save实例 过滤需要选择保存的映射。
            Object oneInst = instMulti.get(0);
            frame.dbTab.mappingRoot.values().stream()
                    .filter(dbCol -> ObjectOF.isNull_.test(FieldOF.get_.apply(dbCol.field,oneInst)))
                    .map(nullCol -> nullCol.getColumn())
                    .forEach(nullCol -> saveChooseMapping.remove(nullCol));
        }

        // 构建获取保存列名序列. [`col1`,col2`]
        saveChooseMapping.keySet().stream()
                .forEach(column -> columnsNameSeq.append($STRICT).append(column).append($STRICT).append($DOT));

        if(columnsNameSeq.length() > 0){
            columnsNameSeq.replace(columnsNameSeq.length() - 1,columnsNameSeq.length(),$EMPTY);
        }else {
            throw new SQLException(frame.database().concat($SPOT).concat(frame.table())
                    .concat(" has no actually columns to save."));
        }

        // 创建value序列. ['val1',null,'va2']
        for (Object oneInst : instMulti) {
            StringBuilder oneValueSeq = new StringBuilder();
            saveChooseMapping.values().stream()
                    .map(dbCol -> FieldOF.get_.apply(dbCol.field,oneInst))
                    .forEach(val -> {
                        if(val == null){
                            oneValueSeq.append(NULL).append($DOT);
                        }else {
                            oneValueSeq.append($SEC).append(_formatVal(val)).append($SEC).append($DOT);
                        }
                    });
            // replace last ',' to "".
            oneValueSeq.replace(oneValueSeq.length() - 1,oneValueSeq.length(),$EMPTY);
            allValueSeq.add(oneValueSeq);
        }

        // 构建SQL Builder.
        StringBuilder SQL = null;

        // 根据SaveMode创建SQL
        switch (saveMode){
            case IGNORE_ERROR:
                SQL = buildInsertIgnore(columnsNameSeq, allValueSeq, frame);
                break;
            case REPLACE_INTO:
                SQL = buildInsertReplace(columnsNameSeq, allValueSeq, frame);
                break;
            case DUPLICATE_UPDATE_ALL:
                SQL = buildDuplicateKey(columnsNameSeq, allValueSeq, saveChooseMapping, saveMode, frame);
                break;
            case DUPLICATE_UPDATE_IGNORE_PRIMARY:
                SQL = buildDuplicateKey(columnsNameSeq, allValueSeq, saveChooseMapping, saveMode, frame);
                break;
            case DUPLICATE_UPDATE_IGNORE_UNIQUE:
                SQL = buildDuplicateKey(columnsNameSeq, allValueSeq, saveChooseMapping, saveMode, frame);
                break;
        }

        return SQL.toString();
    }


    private static final StringBuilder buildInsertIgnore(@NotNull StringBuilder columnsNameSeq,
                                                         @NotNull List<StringBuilder> allValueSeq,
                                                         AbstractSqlProviderFrame frame){
        // 构建SQL Builder.
        StringBuilder builder = new StringBuilder()
                // "insert ignore into "
                .append(INSERT).append($SPACE).append(IGNORE).append($SPACE).append(INTO).append($SPACE)
                // "insert ignore into `database`.`table` "
                .append(frame.database()).append($SPOT).append(frame.table()).append($SPACE)
                // "insert ignore into `database`.`table` [(`col1`,`col2`)] "
                .append($BRACKETS_SMALL_LEFT).append(columnsNameSeq.toString()).append($BRACKETS_SMALL_RIGHT).append($SPACE)
                // "insert ignore into `database`.`table` [(`col1`,`col2`)] values "
                .append(VALUES).append($SPACE);

        // "insert ignore into `database`.`table` [(`col1`,`col2`,`col3`)] values ('va1',null,'col3'),('va1',null,'col3')"
        allValueSeq.forEach(oneValueSeq ->
                builder.append($BRACKETS_SMALL_LEFT)
                        .append(oneValueSeq.toString())
                        .append($BRACKETS_SMALL_RIGHT)
                        .append($DOT)
        );
        builder.replace(builder.length() - 1, builder.length(), $EMPTY);
        return builder;
    }


    private static final StringBuilder buildInsertReplace(@NotNull StringBuilder columnsNameSeq,
                                                          @NotNull List<StringBuilder> allValueSeq,
                                                          AbstractSqlProviderFrame frame){
        // SQL Builder.
        StringBuilder builder = new StringBuilder()
                // "replace into "
                .append(REPLACE).append($SPACE).append(INTO).append($SPACE)
                // "replace into `database`.`table` "
                .append(frame.database()).append($SPOT).append(frame.table()).append($SPACE)
                // "replace into `database`.`table` [(`col1`,`col2`)] "
                .append($BRACKETS_SMALL_LEFT).append(columnsNameSeq.toString()).append($BRACKETS_SMALL_RIGHT).append($SPACE)
                // "replace into `database`.`table` [(`col1`,`col2`)] values "
                .append(VALUES).append($SPACE);

        // "replace into `database`.`table` [(`col1`,`col2`,`col3`)] values ('va1',null,'col3'),('va1',null,'col3')"
        allValueSeq.forEach(oneValueSeq ->
                builder.append($BRACKETS_SMALL_LEFT)
                        .append(oneValueSeq.toString())
                        .append($BRACKETS_SMALL_RIGHT)
                        .append($DOT)
        );
        builder.replace(builder.length() - 1, builder.length(), $EMPTY);
        return builder;
    }


    @SneakyThrows(Throwable.class)
    private static final StringBuilder buildDuplicateKey(@NotNull StringBuilder columnsNameSeq,
                                                         @NotNull List<StringBuilder> allValueSeq,
                                                         @NotNull Map<String, DBCol> saveChooseMapping,
                                                         @NotNull SaveMode saveMode,
                                                         AbstractSqlProviderFrame frame){
        // SQL Builder.
        StringBuilder builder = new StringBuilder()
                // "insert into "
                .append(INSERT).append($SPACE).append(INTO).append($SPACE)
                // "insert into `database`.`table` "
                .append(frame.database()).append($SPOT).append(frame.table()).append($SPACE)
                // "insert into `database`.`table` [(`col1`,`col2`)] "
                .append($BRACKETS_SMALL_LEFT).append(columnsNameSeq.toString()).append($BRACKETS_SMALL_RIGHT).append($SPACE)
                // "insert into `database`.`table` [(`col1`,`col2`)] values "
                .append(VALUES).append($SPACE);

        // "insert into `database`.`table` [(`col1`,`col2`,`col3`)] values ('va1',null,'col3'),('va1',null,'col3') "
        allValueSeq.forEach(oneValueSeq ->
                builder.append($BRACKETS_SMALL_LEFT)
                        .append(oneValueSeq.toString())
                        .append($BRACKETS_SMALL_RIGHT)
                        .append($DOT)
        );
        builder.replace(builder.length() - 1, builder.length(), $EMPTY);

        // create duplicate issue builder.
        final StringBuilder duplicateValuesBuild = new StringBuilder();

        // create duplicate fields update seq generic.
        Consumer<Collection<String>> $duplicate_columns_update_generic = columns ->
                // "`column1` = values(`column1`),`column2` = values(`column2`),"
                columns.stream().forEach(column -> duplicateValuesBuild.append($STRICT).append(column).append($STRICT)
                        .append($SPACE).append($EQ).append($SPACE).append(VALUES).append($BRACKETS_SMALL_LEFT)
                        .append($STRICT).append(column).append($STRICT).append($BRACKETS_SMALL_RIGHT).append($DOT));

        // predicate SaveMode, build sql.
        switch (saveMode){

            // all keys which been submitted to update.
            case DUPLICATE_UPDATE_ALL:
                $duplicate_columns_update_generic.accept(saveChooseMapping.keySet());
                break;

            // when column declared with 'Primary', ignore these keys's update.
            case DUPLICATE_UPDATE_IGNORE_PRIMARY:
                List<String> submitted_columns_for_update_except_primary = saveChooseMapping.values().stream()
                        .filter(dbCol -> dbCol.primary == null)
                        .map(dbCol -> dbCol.column).collect(Collectors.toList());
                $duplicate_columns_update_generic.accept(submitted_columns_for_update_except_primary);
                break;

            // when column declared with 'Primary' or 'Unique', ignore these keys's update.
            case DUPLICATE_UPDATE_IGNORE_UNIQUE:
                List<String> submitted_columns_for_update_except_unique = saveChooseMapping.values().stream()
                        .filter(dbCol -> dbCol.primary == null && dbCol.unique == null)
                        .map(dbCol -> dbCol.column).collect(Collectors.toList());
                $duplicate_columns_update_generic.accept(submitted_columns_for_update_except_unique);
                break;

            default:
                /* by this case, using invoke this method by reflection, funny action ~.~ */
                break;
        }

        if(duplicateValuesBuild.length() == 0){
            throw new SQLException(frame.database().concat($SPOT).concat(frame.table())
                    .concat(" execute command [on duplicate key update] has no actually columns to update when occur duplicate."));
        }else {
            duplicateValuesBuild.replace(duplicateValuesBuild.length() - 1, duplicateValuesBuild.length(), $EMPTY);
            // filling save sql.
            builder
                    // " on duplicate key update "
                    .append($SPACE).append(ON_DUPLICATE_KEY_UPDATE).append($SPACE)
                    // " on duplicate key update [`col1` = values(`col1`),`col2` = values(`col2`)]"
                    .append(duplicateValuesBuild.toString());
        }

        return builder;
    }


}